package sanrenxing.dayEnd.backRepertoryTotal.dao;

import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.StatementType;
import sanrenxing.dayEnd.backRepertoryTotal.domain.FundAllocation;
import sanrenxing.dayEnd.backRepertoryTotal.domain.SecuritiesStock;

import java.util.List;

public interface RepertoryTotalDao {

    /**
     * 调用处理五大库存的oracle过程
     */
    @Select({"call add_CashAccount_to_CashTotal()"})
    @Options(statementType=StatementType.CALLABLE)
    public void updateRepertory();


    /**
     * 通过用户id查询用户名称
     * @param userId
     * @return
     */
    @Select("select userName from users where userId=#{userId}")
    public String queryUserName(int userId);

    /**
     * 查询基金
     */
    @Select("select fundId from fundSetup where 1>=rownum and rownum>0")
    public String queryFundId();

    /**
     * 该方法用于将证券应收应付库存统计到证券库存
     */
    @Select("call add_ReStock_Total()")
    public void reToStockTotal();

    /**
     * 通过聚合函数将证券库存统计相同证券编号的数据统计成一条，并且计算其单位价格,判断是否知股票
     * @return
     */
    @Select("select ss.seId as seId,(sum(ss.sumMoney)/sum(ss.secNum)) as costs,sum(ss.secNum) as secNum,sum(ss.sumMoney) as sumMoney," +
            "sysdate as dates,min(ss.fundId) as fundId,avg(ss.sign) as sign,min(ss.caId) as caId from " +
            "securitiesStock ss join financialsecurities fs on ss.seId=fs.seId where fs.seType=1 group by ss.seId")
    public List<SecuritiesStock> TotalReStock();

    /**
     * 通过聚合函数将证券库存统计相同证券编号的数据统计成一条，并且计算其单位价格,判断是否知债券
     * @return
     */
    @Select("select ss.seId as seId,(sum(ss.sumMoney)/sum(ss.secNum)) as costs,sum(ss.secNum) as secNum,sum(ss.sumMoney) as sumMoney," +
            "sysdate as dates,min(ss.fundId) as fundId,avg(ss.sign) as sign,min(ss.caId) as caId from " +
            "securitiesStock ss join financialsecurities fs on ss.seId=fs.seId where fs.seType=2 group by ss.seId")
    public List<SecuritiesStock> TotalReStockBond();

    /**
     * 将统计好的证券库存添加到证券库存表当中
     * @param securitiesStock
     */
    @Insert("insert into securitiesStock(secId,seId,costs,secNum,sumMoney,dates,fundId,sign,caId) " +
            "values(seqSecuritiesStock.nextval,#{seId},#{costs},#{secNum},#{sumMoney},to_date('${dates}','yyyy-MM-dd')," +
            "#{fundId},#{sign},#{caId})")
    public void addReStock(SecuritiesStock securitiesStock);

    /**
     * 删除所有证券库存
     */
    @Delete("delete from securitiesStock")
    public void deleteReStock();

    /**
     * 将现金应收应付库存添加到ta库存
     */
    @Select("call add_CashStock_Total()")
    public void addCashToTaStock();

    /**
     * 统计现金库存管理行数
     * @return
     */
    @Select("<script>" +
            "Select count(*) from cashStock " +
            "<where>" +
            "<if test='date!=null'> " +
            " time=to_date('${date}','yyyy-MM-dd')" +
            "</if>" +
            "</where>" +
            "</script>")
    public int cashStockCount(String date);

    /**
     * 统计证券库存管理行数
     * @return
     */
    @Select("<script>" +
            "Select count(*) from securitiesStock " +
            "<where>" +
            "<if test='date!=null'> " +
            " dates=to_date('${date}','yyyy-MM-dd')" +
            "</if>" +
            "</where>" +
            "</script>")
    public int securitiesStockCount(String date);

    /**
     * 统计证券应收应付库存行数
     * @return
     */
    @Select("<script>" +
            "Select count(*) from secRecAndPayStock " +
            "<where>" +
            "<if test='date!=null'> " +
            " Dates=to_date('${date}','yyyy-MM-dd')" +
            "</if>" +
            "</where>" +
            "</script>")
    public int secRecAndPayStockCount(String date);

    /**
     * 统计现金应收应付库存行数
     * @return
     */
    @Select("<script>" +
            "Select count(*) from cashRecAndPayStock " +
            "<where>" +
            "<if test='date!=null'> " +
            " Dates=to_date('${date}','yyyy-MM-dd')" +
            "</if>" +
            "</where>" +
            "</script>")
    public int cashRecAndPayStockCount(String date);

    /**
     * 统计ta库存行数
     * @return
     */
    @Select("<script>" +
            "Select count(*) from taStock " +
            "<where>" +
            "<if test='date!=null'> " +
            " dates=to_date('${date}','yyyy-MM-dd')" +
            "</if>" +
            "</where>" +
            "</script>")
    public int taStockCount(String date);

    /**
     * 将当日需处理的资金调拨数据取出
     * @param today
     * @return
     */
    @Select("select * from fundAllocation where mark='未处理' and to_char(reDate,'yyyy-MM-dd')=#{today}")
    public List<FundAllocation> queryMoneyTransferToCashStock(@Param("today") String today);

    /**
     * 将资金调拨数据的标记改为已处理
     */
    @Update("update fundAllocation set mark='已处理' where mark='未处理' and to_char(reDate,'yyyy-MM-dd')=#{toDay}")
    public void updateFundData(String toDay);

    /**
     * 处理资金调拨时流入金额需添加到现金库存
     */
    @Update("update cashStock set caMoney=caMoney+#{money},time=to_date('${toDay}','yyyy-MM-dd')")
    public void updateCashStockInto(@Param("money") double money,@Param("toDay") String toDay);

    /**
     * 处理资金调拨时流出金额需添加到现金库存
     * @param money
     * @param toDay
     */
    @Update("update cashStock set caMoney=caMoney-#{money},time=to_date('${toDay}','yyyy-MM-dd')")
    public void updateCashStockOut(@Param("money") double money,@Param("toDay") String toDay);

    /**
     * 查询现金库存总行数
     * @return
     */
    @Select("select count(*) from cashStock")
    public int cashStockRowNum();

    /**
     * 当把资金调拨数据添加到现金库存时而现金库存没有数据，则先插入一行数据
     */
    @Insert("insert into cashStock(cashId,fundId,caId,caMoney,time) " +
            "values(seqCashStock.nextval,#{fundId},#{caId},#{money},to_date('${toDay}','yyyy-MM-dd'))")
    public void ifNulladdCashStock(@Param("fundId") String fundId,@Param("caId") int caId,@Param("money") double money,@Param("toDay") String toDay);
}
